using System;
using System.Collections;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Linq;
using NHibernate.SqlCommand;
using NHibernate.Transform;

namespace Infrastucture
{
    public class BTBRepository : BaseNHibernateRepository<BTB>
    {
        public BTBRepository(ISessionStorage sessionStorage)
            : base(sessionStorage)
        {
        }

        public BTBRepository(ISessionStorage sessionStorage, ISession session)
            : base(sessionStorage, session)
        {
        }


        public string GetMaxNumber(string kodeAwal)
        {
            string res = string.Empty;
            var lenKode = kodeAwal.Length + 10;
            var query = Session.CreateSQLQuery("Select Max(NoBTB) as Number from BTB where NoBTB like'" + kodeAwal + "%' AND   Len(NoBTB)=" + lenKode).AddScalar("Number", NHibernateUtil.String);
            var objRes = query.List()[0];
            if (objRes != null) res = objRes.ToString();
            return res;
        }

        public void UpdatePurchaseInvoice(string NoBtb,string PINo)
        {
            ISQLQuery command =
              Session.CreateSQLQuery("Update BTB set PINo='" + PINo + "',Status=1 where NoBtb='" + NoBtb + "'");
            int res = command.ExecuteUpdate();
        }

        public  IList<BTB> GetDataPaged(int page, int pageSize, string sortProperty="", string sortType="")
        {
            var Results = new List<BTB>();

            using (ITransaction tx = Session.BeginTransaction())
            {
                ICriteria criteria = Session.CreateCriteria<BTB>()
                    .CreateAlias("Supplier", "sp", JoinType.InnerJoin)
                    .CreateAlias("PurchaseOrder", "po", JoinType.LeftOuterJoin)
                    .SetProjection(Projections.ProjectionList()
                                       .Add(Projections.Property("NoBtb"))
                                       .Add(Projections.Property("Description"))
                                       .Add(Projections.Property("Status"))
                                       .Add(Projections.Property("TglBtb"))
                                       .Add(Projections.Property("PINo"))
                                       .Add(Projections.Property("po.PONumber"))
                                       .Add(Projections.Property("sp.SupplierName")));
                   
                    

                if (sortType != "")
                {
                     if (sortType.ToUpper() == "ASC")
                        criteria.AddOrder(new Order(sortProperty, true));
                     else
                        criteria.AddOrder(new Order(sortProperty, false));
                }

                criteria.SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize);

                ICriteria criteriacount =
                    Session.CreateCriteria<BTB>()
                    .SetProjection(Projections.RowCountInt64());

                IList Res = Session.CreateMultiCriteria().Add(criteria).Add(criteriacount).List();

                foreach (object o in (IList)Res[0])
                {
                    var arrlist = o as Object[];
                    var btb = new BTB();
                    btb.NoBtb = arrlist[0].ToString();
                    btb.Description =  arrlist[1] != null ? arrlist[1].ToString() : string.Empty;
                    btb.Status =  arrlist[2] != null ? Convert.ToInt16(arrlist[2]) : 0;
                    btb.TglBtb =  Convert.ToDateTime(arrlist[3]);
                    btb.PINo =  arrlist[4] != null ? arrlist[4].ToString() : string.Empty;
                    btb.PurchaseOrder = new PurchaseOrder() {PONumber =  arrlist[5] != null ? arrlist[5].ToString() : string.Empty};
                    btb.Supplier = new Supplier() {SupplierName =  arrlist[6] != null ? arrlist[6].ToString() : string.Empty };
                    Results.Add(btb);
                }

                RecordCount =  (long)((IList)Res[1])[0];
                tx.Commit();
            }

            return Results;
        }

        public void UpdateEmailStatus(string NoBtb)
        {
            ISQLQuery command =
            Session.CreateSQLQuery("Update Btb set HasSent=1 Where NoBtb='" + NoBtb + "'");
            int res = command.ExecuteUpdate();
        }

        //public override IList<BTB>  GetDataPaged(Dictionary<string,object> Criteria, string expression, int page, int pageSize)
        //{
        //    var Results = new List<BTB>();

        //    using (ITransaction tx = Session.BeginTransaction())
        //    {
        //        ICriteria criteria = Session.CreateCriteria<BTB>()
        //            .CreateAlias("Supplier", "sp", JoinType.InnerJoin)
        //            .CreateAlias("PurchaseOrder", "po", JoinType.LeftOuterJoin)
        //            .SetProjection(Projections.ProjectionList()
        //                               .Add(Projections.Property("NoBtb"))
        //                               .Add(Projections.Property("Description"))
        //                               .Add(Projections.Property("Status"))
        //                               .Add(Projections.Property("TglBtb"))
        //                               .Add(Projections.Property("PINo"))
        //                               .Add(Projections.Property("po.PONumber"))
        //                               .Add(Projections.Property("sp.SupplierName")));
        //        criteria.SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize);

        //        var filter = new List<string>();
        //        int i = 0;
        //        foreach (var criterion in Criteria)
        //            if (
        //                !typeof(BTB).GetProperties().Any(
        //                    x => x.Name == criterion.Key.Split(new[] { "." }, StringSplitOptions.None)[0]))
        //                filter.Add(criterion.Key);

        //        filter.ForEach((x) => { Criteria.Remove(x); }
        //            );
        //        dynamic exp = null;
        //        if (expression == "AND")
        //            exp = Expression.Conjunction();

        //        else
        //            exp = Expression.Disjunction();

        //        foreach (var criterion in Criteria)
        //        {
        //            if (criterion.Value == null) continue;

        //            if (criterion.Key == "PurchaseOrder")
        //            {
        //                exp.Add(Expression.Sql(string.Format("po.PONumber like'%{0}%'",criterion.Value)));
        //                continue;
        //            }

        //            if (criterion.Key == "Supplier")
        //            {
        //                exp.Add(Expression.Sql(string.Format("SupplierId like'%{0}%'", criterion.Value)));
        //                continue;
        //            }

        //            if (criterion.Value.GetType() == typeof(string))
        //                exp.Add(Expression.Like(criterion.Key, criterion.Value.ToString(), MatchMode.Anywhere));
        //            else
        //            {
        //                exp.Add(Expression.Eq(criterion.Key, criterion.Value));
        //            }
        //            i += 1;
        //        }

        //        criteria.Add(exp);

        //        ICriteria criteriacount =
        //            Session.CreateCriteria<BTB>()
        //            .CreateAlias("Supplier", "sp", JoinType.InnerJoin)
        //            .CreateAlias("PurchaseOrder", "po", JoinType.LeftOuterJoin)
        //            .Add(exp)
        //            .SetProjection(Projections.RowCountInt64());

        //        IList Res = Session.CreateMultiCriteria().Add(criteria).Add(criteriacount).List();

        //        foreach (object o in (IList)Res[0])
        //        {
        //            var arrlist = o as Object[];
        //            var btb = new BTB();
        //            btb.NoBtb = arrlist[0].ToString();
        //            btb.Description = arrlist[1] != null ? arrlist[1].ToString() : string.Empty;
        //            btb.Status = arrlist[2] != null ? Convert.ToInt16(arrlist[2]) : 0;
        //            btb.TglBtb = Convert.ToDateTime(arrlist[3]);
        //            btb.PINo = arrlist[4] != null ? arrlist[4].ToString() : string.Empty;
        //            btb.PurchaseOrder = new PurchaseOrder() { PONumber = arrlist[5] != null ? arrlist[5].ToString() : string.Empty };
        //            btb.Supplier = new Supplier() { SupplierName = arrlist[6] != null ? arrlist[6].ToString() : string.Empty };
        //            Results.Add(btb);
        //        }

        //        RecordCount = (long)((IList)Res[1])[0];
        //        tx.Commit();
        //    }

        //    return Results;
        //}

    
    }
}